/**
 * @swagger
 * /api/asset/category-stats:
 *   get:
 *     tags:
 *       - 资产管理
 *     summary: 获取资产分类统计数据
 *     description: 按资产类别统计数量、价值和分布情况，支持时间筛选
 *     parameters:
 *       - in: query
 *         name: startDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 开始日期 (YYYY-MM-DD)
 *         example: "2023-01-01"
 *       - in: query
 *         name: endDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 结束日期 (YYYY-MM-DD)
 *         example: "2023-12-31"
 *     responses:
 *       200:
 *         description: 成功获取分类统计数据
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 code:
 *                   type: integer
 *                   example: 200
 *                   description: 响应状态码
 *                 message:
 *                   type: string
 *                   example: "分类统计获取成功"
 *                   description: 响应消息
 *                 data:
 *                   type: object
 *                   properties:
 *                     ggjClassification:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           example: "国管局分类统计"
 *                         categories:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               categoryName:
 *                                 type: string
 *                                 description: 分类名称
 *                                 example: "办公设备"
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                                 example: 150
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                                 example: "1250000.50"
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                                 example: "8333.34"
 *                               totalArea:
 *                                 type: string
 *                                 description: 总面积
 *                                 example: "2500.75"
 *                               totalFiscalFunding:
 *                                 type: string
 *                                 description: 财政拨款总额
 *                                 example: "1000000.00"
 *                               valuePercentage:
 *                                 type: string
 *                                 description: 价值占比
 *                                 example: "25.30"
 *                     gb22Classification:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           example: "新国标分类统计"
 *                         categories:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               categoryName:
 *                                 type: string
 *                                 description: 分类名称
 *                                 example: "通用设备"
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                                 example: 120
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                                 example: "980000.25"
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                                 example: "8166.67"
 *                               totalArea:
 *                                 type: string
 *                                 description: 总面积
 *                                 example: "1800.50"
 *                               totalFiscalFunding:
 *                                 type: string
 *                                 description: 财政拨款总额
 *                                 example: "750000.00"
 *                               valuePercentage:
 *                                 type: string
 *                                 description: 价值占比
 *                                 example: "19.80"
 *                     comparison:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           example: "分类体系对比"
 *                         systems:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               classificationType:
 *                                 type: string
 *                                 description: 分类体系类型
 *                                 example: "GGJ分类"
 *                               categoryCount:
 *                                 type: integer
 *                                 description: 分类数量
 *                                 example: 12
 *                               totalAssets:
 *                                 type: integer
 *                                 description: 总资产数
 *                                 example: 1250
 *                               avgAssetsPerCategory:
 *                                 type: string
 *                                 description: 每分类平均资产数
 *                                 example: "104.17"
 *                     topCategories:
 *                       type: object
 *                       properties:
 *                         byValue:
 *                           type: object
 *                           properties:
 *                             title:
 *                               type: string
 *                               example: "价值最高的分类(Top5)"
 *                             categories:
 *                               type: array
 *                               items:
 *                                 type: object
 *                                 properties:
 *                                   categoryName:
 *                                     type: string
 *                                     description: 分类名称
 *                                     example: "车辆"
 *                                   totalValue:
 *                                     type: string
 *                                     description: 总价值
 *                                     example: "2500000.00"
 *                                   assetCount:
 *                                     type: integer
 *                                     description: 资产数量
 *                                     example: 25
 *                         byCount:
 *                           type: object
 *                           properties:
 *                             title:
 *                               type: string
 *                               example: "数量最多的分类(Top5)"
 *                             categories:
 *                               type: array
 *                               items:
 *                                 type: object
 *                                 properties:
 *                                   categoryName:
 *                                     type: string
 *                                     description: 分类名称
 *                                     example: "办公设备"
 *                                   assetCount:
 *                                     type: integer
 *                                     description: 资产数量
 *                                     example: 350
 *                                   totalValue:
 *                                     type: string
 *                                     description: 总价值
 *                                     example: "1800000.50"
 *       500:
 *         description: 服务器内部错误
 */
import { withCors } from '../../../lib/cors';
import { success, error as errorResponse, handleResponse } from '../../../lib/response';
import { query } from '../../../lib/db';

/**
 * 处理资产分类统计请求
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 */
async function handler(req, res) {
  try {
    const { startDate, endDate } = req.query;

    let whereClause = '';
    const params = [];

    if (startDate && endDate) {
      whereClause = 'WHERE PURCHASE_DATE BETWEEN ? AND ?';
      params.push(startDate, endDate);
    } else if (startDate) {
      whereClause = 'WHERE PURCHASE_DATE >= ?';
      params.push(startDate);
    } else if (endDate) {
      whereClause = 'WHERE PURCHASE_DATE <= ?';
      params.push(endDate);
    }
    
    // 国管局分类统计
    const ggjStatsQuery = `
      SELECT 
        COALESCE(ASSETS_SORT_GGJ_NAME, '未分类') as categoryName,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue,
        SUM(COALESCE(CHEKMJ, 0)) as totalArea,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY ASSETS_SORT_GGJ_NAME
      ORDER BY assetCount DESC
    `;

    const ggjResult = await query(ggjStatsQuery, params);

    // 新国标分类统计
    const gb22StatsQuery = `
      SELECT 
        COALESCE(ASSETS_SORT_GB22_NAME, '未分类') as categoryName,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue,
        SUM(COALESCE(CHEKMJ, 0)) as totalArea,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY ASSETS_SORT_GB22_NAME
      ORDER BY assetCount DESC
    `;

    const gb22Result = await query(gb22StatsQuery, params);

    // 分类对比分析
    const comparisonQuery = `
      SELECT 
        'GGJ分类' as classificationType,
        COUNT(DISTINCT ASSETS_SORT_GGJ_NAME) as categoryCount,
        COUNT(*) as totalAssets,
        AVG(asset_per_category) as avgAssetsPerCategory
      FROM (
        SELECT ASSETS_SORT_GGJ_NAME, COUNT(*) as asset_per_category
        FROM wd_sec_assets12
        ${whereClause}
        GROUP BY ASSETS_SORT_GGJ_NAME
      ) ggj_stats
      UNION ALL
      SELECT 
        'GB22分类' as classificationType,
        COUNT(DISTINCT ASSETS_SORT_GB22_NAME) as categoryCount,
        COUNT(*) as totalAssets,
        AVG(asset_per_category) as avgAssetsPerCategory
      FROM (
        SELECT ASSETS_SORT_GB22_NAME, COUNT(*) as asset_per_category
        FROM wd_sec_assets12
        ${whereClause}
        GROUP BY ASSETS_SORT_GB22_NAME
      ) gb22_stats
    `;

    const comparisonResult = await query(comparisonQuery, [...params, ...params]);

    // 高价值分类Top5
    const topValueCategoriesQuery = `
      SELECT 
        'GGJ' as type,
        COALESCE(ASSETS_SORT_GGJ_NAME, '未分类') as categoryName,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        COUNT(*) as assetCount
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY ASSETS_SORT_GGJ_NAME
      HAVING totalValue > 0
      ORDER BY totalValue DESC
      LIMIT 5
    `;

    const topValueResult = await query(topValueCategoriesQuery, params);

    // 数量最多分类Top5
    const topCountCategoriesQuery = `
      SELECT 
        'GGJ' as type,
        COALESCE(ASSETS_SORT_GGJ_NAME, '未分类') as categoryName,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY ASSETS_SORT_GGJ_NAME
      ORDER BY assetCount DESC
      LIMIT 5
    `;

    const topCountResult = await query(topCountCategoriesQuery, params);

    const responseData = {
      ggjClassification: {
        title: "国管局分类统计",
        categories: ggjResult.map(row => ({
          categoryName: row.categoryName,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2),
          totalArea: parseFloat(row.totalArea || 0).toFixed(2),
          totalFiscalFunding: parseFloat(row.totalFiscalFunding || 0).toFixed(2),
          valuePercentage: 0 // Will be calculated below
        }))
      },
      gb22Classification: {
        title: "新国标分类统计", 
        categories: gb22Result.map(row => ({
          categoryName: row.categoryName,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2),
          totalArea: parseFloat(row.totalArea || 0).toFixed(2),
          totalFiscalFunding: parseFloat(row.totalFiscalFunding || 0).toFixed(2),
          valuePercentage: 0 // Will be calculated below
        }))
      },
      comparison: {
        title: "分类体系对比",
        systems: comparisonResult.map(row => ({
          classificationType: row.classificationType,
          categoryCount: parseInt(row.categoryCount),
          totalAssets: parseInt(row.totalAssets),
          avgAssetsPerCategory: parseFloat(row.avgAssetsPerCategory || 0).toFixed(2)
        }))
      },
      topCategories: {
        byValue: {
          title: "价值最高的分类(Top5)",
          categories: topValueResult.map(row => ({
            categoryName: row.categoryName,
            totalValue: parseFloat(row.totalValue || 0).toFixed(2),
            assetCount: parseInt(row.assetCount)
          }))
        },
        byCount: {
          title: "数量最多的分类(Top5)",
          categories: topCountResult.map(row => ({
            categoryName: row.categoryName,
            assetCount: parseInt(row.assetCount),
            totalValue: parseFloat(row.totalValue || 0).toFixed(2)
          }))
        }
      }
    };

    // 计算百分比
    const ggjTotalValue = responseData.ggjClassification.categories.reduce((sum, cat) => sum + parseFloat(cat.totalValue), 0);
    const gb22TotalValue = responseData.gb22Classification.categories.reduce((sum, cat) => sum + parseFloat(cat.totalValue), 0);

    responseData.ggjClassification.categories.forEach(cat => {
      cat.valuePercentage = ggjTotalValue > 0 ? ((parseFloat(cat.totalValue) / ggjTotalValue) * 100).toFixed(2) : "0.00";
    });

    responseData.gb22Classification.categories.forEach(cat => {
      cat.valuePercentage = gb22TotalValue > 0 ? ((parseFloat(cat.totalValue) / gb22TotalValue) * 100).toFixed(2) : "0.00";
    });

    return handleResponse(res, success(responseData, "资产分类统计获取成功"));
    
  } catch (error) {
    console.error('Asset category stats error:', error);
    return handleResponse(res, errorResponse("获取资产分类统计失败", 500));
  }
}

export default withCors(handler, ['GET']);